package doughnut.xc.Demo;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import java.io.InputStream;

/**
 * 百万数据 Excel解析器
 */
public class ExcelParser {

    public void parse(String path) throws Exception {
        // 1.根据Excel获取OPCPackage对象
        OPCPackage pkg = OPCPackage.open(path, PackageAccess.READ);
        try {
            // 2.创建XSSFReader对象
            XSSFReader reader = new XSSFReader(pkg);
            // 3.获取SharedStringsTable对象
            SharedStringsTable sst = reader.getSharedStringsTable();
            // 4.获取StylesTable对象
            StylesTable styles = reader.getStylesTable();

            /* 过时！！！
            XMLReader parser = XMLReaderFactory.createXMLReader();
            // 处理公共属性：Sheet名，Sheet合并单元格
            parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst, new SheetHandle(), false));
            XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) reader.getSheetsData();
             */

            // 5.创建SAXParserFactory实例
            SAXParserFactory saxParserFactory = SAXParserFactory.newInstance();
            // ！！！必须加设置命名空间感知，因为Excel的XML文件中使用了命名空间
            saxParserFactory.setNamespaceAware(true);
            // 6.创建SAXParser实例
            SAXParser saxParser = saxParserFactory.newSAXParser();
            // 7.获取XMLReader实例
            XMLReader parser = saxParser.getXMLReader();
            // 8.设置内容处理器为XSSFSheetXMLHandler对象
            parser.setContentHandler(new XSSFSheetXMLHandler(styles, sst, new SheetHandle(), false));
            // 9.获取Sheet迭代器并遍历所有Sheet
            XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) reader.getSheetsData();
            while (sheets.hasNext()) {
                InputStream sheetStream = sheets.next();
                InputSource sheetSource = new InputSource(sheetStream);
                try {
                    // 使用XMLReader解析Sheet内容
                    parser.parse(sheetSource);
                } finally {
                    sheetStream.close();
                }
            }
        } finally {
            pkg.close();
        }
    }
}
